{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This code allows to compute the baseline elasticity measures in the paper\n",
    "# for a PSEUDO data file containing multiple bids for one auction\n",
    "# This file allows to compute ME, SE, TE and GE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from numpy.polynomial.polynomial import polyfit\n",
    "import math\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import StrMethodFormatter\n",
    "from sklearn import metrics\n",
    "from sklearn import linear_model\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.model_selection import train_test_split \n",
    "import statsmodels.api as sm\n",
    "import scipy.stats\n",
    "from scipy.stats import norm\n",
    "from scipy.stats import ttest_1samp\n",
    "import math\n",
    "import datetime \n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.drawing.image import Image\n",
    "pd.options.display.max_columns = None\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read raw file\n",
    "auctions = pd.read_excel('Auction_PSEUDO.xlsx')\n",
    "     \n",
    "# change name of columns    \n",
    "auctions['Size']=auctions['Total allocation']\n",
    "auctions['PCUT']=auctions['AuctionPrice']\n",
    "auctions['Price']=auctions['BidPrice']\n",
    "auctions['Bid']=auctions['BidQuantity']\n",
    "auctions['Allocation']=auctions['Awarded']\n",
    "auctions['DealerAnon']=auctions['dealer']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Comp_auctions represents the competitive phase of the auctions\n",
    "Comp_auctions=auctions.copy()\n",
    "\n",
    "# Creating a dataframe with all the prices and the total quantity demanded ('bid') at that price\n",
    "dem_curves = Comp_auctions[['Auction_nr_Comp','Price','Bid','PCUT','DealerAnon']].groupby(['Auction_nr_Comp','Price']).agg({'Bid':'sum','PCUT':'mean'}).reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "auctionslist = list(dem_curves['Auction_nr_Comp'].unique())\n",
    "dem_curves_dict = {}\n",
    "for auction in auctionslist:\n",
    "    dem_curves_dict[auction] = dem_curves.groupby(dem_curves.Auction_nr_Comp).get_group(auction).sort_values(by=['Price'],ascending=False)\n",
    "    dem_curves_dict[auction].Bid = dem_curves_dict[auction].Bid.cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Auction_nr_Comp\n",
       "1    213.321639\n",
       "Name: TE, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total Elasticity \n",
    "\n",
    "TE =dem_curves.set_index(['Auction_nr_Comp']).sort_values(by=['Auction_nr_Comp','Price'],ascending=False)\n",
    "TE['Bid'] = TE.groupby('Auction_nr_Comp')['Bid'].agg('cumsum')\n",
    "TE['sum all Ps']=TE.groupby('Auction_nr_Comp').apply(lambda x: x['Price'].sum())\n",
    "TE['sum all Qs']=TE.groupby('Auction_nr_Comp').apply(lambda x: x['Bid'].sum())\n",
    "TE['total slope']=TE.groupby(['Auction_nr_Comp']).apply(lambda x: polyfit(x['Price'], x['Bid'], 1)[1])\n",
    "TE['P/Q']=TE['sum all Ps']/TE['sum all Qs']\n",
    "TE['P threshold'] = TE['PCUT']\n",
    "TE = TE[round(TE['Price'],6) == round(TE['PCUT'],6)]\n",
    "TE['Q threshold'] = TE['Bid']\n",
    "\n",
    "TE['TE']=TE['total slope']*TE['P threshold']/TE['Q threshold']\n",
    "TE=TE['TE'].abs()\n",
    "\n",
    "TE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Auction_nr_Comp\n",
       "1    190.662802\n",
       "Name: GE, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Gross Elasticity \n",
    "\n",
    "GE =dem_curves.set_index('Auction_nr_Comp').sort_values(by=['Auction_nr_Comp','Price'],ascending=False)\n",
    "GE['Bid'] = GE.groupby('Auction_nr_Comp')['Bid'].agg('cumsum')\n",
    "GE['Ptop'] = GE.groupby('Auction_nr_Comp').apply(lambda x: x['Price'].max())\n",
    "GE['Qtop'] = GE.groupby('Auction_nr_Comp').apply(lambda x: x['Bid'].min())\n",
    "GE['P threshold'] = GE['PCUT']\n",
    "GE = GE[round(GE['Price'],6) == round(GE['PCUT'],6)]\n",
    "GE['Q threshold'] = GE['Bid']\n",
    "\n",
    "GE['GE'] = ((GE['Q threshold'] - GE['Qtop'])/(GE['Q threshold'])) / ((GE['P threshold'] - GE['Ptop'])/(GE['P threshold']))\n",
    "GE=GE['GE'].abs()\n",
    "\n",
    "GE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Auction_nr_Comp\n",
       "1    538.649611\n",
       "Name: SE, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Subscribed Elasticity\n",
    "\n",
    "SE =dem_curves.set_index('Auction_nr_Comp').sort_values(by=['Auction_nr_Comp','Price'],ascending=False)\n",
    "SE['Bid'] = SE.groupby('Auction_nr_Comp')['Bid'].agg('cumsum')\n",
    "\n",
    "SE = SE[round(SE['Price'],6)>=round(SE['PCUT'],6)]\n",
    "SE.reset_index(inplace=True)\n",
    "SE = SE.groupby(['Auction_nr_Comp']).apply(lambda x: x.iloc[-5:,:])\n",
    "SE.reset_index(drop=True, inplace=True)\n",
    "SE.set_index('Auction_nr_Comp', inplace=True)\n",
    "SE['marginal slope']=SE.groupby(['Auction_nr_Comp']).apply(lambda x: polyfit(x['Price'], x['Bid'], 1)[1])\n",
    "SE['sum all Ps']=SE.groupby('Auction_nr_Comp').apply(lambda x: x['Price'].sum())\n",
    "SE['sum all Qs']=SE.groupby('Auction_nr_Comp').apply(lambda x: x['Bid'].sum())\n",
    "SE['P/Q']=SE['sum all Ps']/SE['sum all Qs']\n",
    "SE['P threshold'] = SE['PCUT']\n",
    "SE = SE[round(SE['Price'],6) == round(SE['PCUT'],6)]\n",
    "SE['Q threshold'] = SE['Bid']\n",
    "\n",
    "SE['SE']=SE['marginal slope']*SE['P threshold']/SE['Q threshold']\n",
    "SE=SE['SE'].abs()\n",
    "\n",
    "SE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "auctions2 = auctions.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Comp_auctions represents the competitive phase of the auctions\n",
    "Comp_auctions5=auctions2.copy()\n",
    "# Creating a dataframe with all the prices and the total quantity demanded ('bid') at that price\n",
    "dem_curves1 = Comp_auctions5[['Auction_nr_Comp','Price','Bid','PCUT','DealerAnon','Allocation']].groupby(['Auction_nr_Comp','Price']).agg({'Bid':'sum','PCUT':'mean','Allocation':'sum'}).reset_index()\n",
    "dem_curves1 = dem_curves1.sort_values(by=['Auction_nr_Comp','Price'],ascending=False)\n",
    "dem_curves1['Bid-Allocated'] = 0\n",
    "dem_curves1['Bid-Allocated'] = np.where(dem_curves1['PCUT'] == dem_curves1['Price'],dem_curves1['Bid']-dem_curves1['Allocation'],0) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "New = pd.DataFrame(columns = dem_curves1.columns )\n",
    "for i in dem_curves1.index:\n",
    "    if dem_curves1.loc[i,'Bid-Allocated'] != 0:\n",
    "        New.loc[i,:] = dem_curves1.loc[i,:]\n",
    "New['Price'] = New['Price'] - 0.00001\n",
    "\n",
    "New['Bid'] = New['Bid-Allocated']  \n",
    "for i in dem_curves1.index:\n",
    "    if dem_curves1.loc[i,'Bid-Allocated'] != 0:\n",
    "        dem_curves1.loc[i,'Bid'] = dem_curves1.loc[i,'Allocation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dem_curves2 = pd.concat([dem_curves1, New], ignore_index = True, axis = 0)\n",
    "dem_curves2 = dem_curves2.sort_values(['Auction_nr_Comp', 'Price'], ascending=[True, False])\n",
    "dem_curves2['Bid'] = pd.to_numeric(dem_curves2['Bid'], downcast=\"integer\")\n",
    "dem_curves2['Price'] = pd.to_numeric(dem_curves2['Price'], downcast=\"float\")\n",
    "dem_curves2['PCUT'] = pd.to_numeric(dem_curves2['PCUT'], downcast=\"float\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Auction_nr_Comp\n",
       "1.0    244.189751\n",
       "Name: ME, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Marginal Elasticity\n",
    "\n",
    "ME =dem_curves2.set_index('Auction_nr_Comp').sort_values(by=['Auction_nr_Comp','Price'],ascending=False)\n",
    "ME['Bid'] = ME.groupby('Auction_nr_Comp')['Bid'].agg('cumsum')\n",
    "ME =ME[round(ME['Price'],6)<=round(ME['PCUT'],6)]\n",
    "ME.reset_index(inplace=True)\n",
    "ME = ME.groupby(['Auction_nr_Comp']).apply(lambda x: x.iloc[:6,:])\n",
    "ME.reset_index(drop=True, inplace=True)\n",
    "ME.set_index('Auction_nr_Comp', inplace=True)\n",
    "ME['marginal slope']=ME.groupby(['Auction_nr_Comp']).apply(lambda x: polyfit(x['Price'], x['Bid'], 1)[1])\n",
    "ME['sum all Ps']=ME.groupby('Auction_nr_Comp').apply(lambda x: x['Price'].sum())\n",
    "ME['sum all Qs']=ME.groupby('Auction_nr_Comp').apply(lambda x: x['Bid'].sum())\n",
    "ME['P/Q']=ME['sum all Ps']/ME['sum all Qs']\n",
    "ME['P threshold'] = ME['PCUT']\n",
    "ME = ME.drop_duplicates(subset=['marginal slope', 'sum all Ps','sum all Qs'])\n",
    "ME['Q threshold'] = ME['Bid']\n",
    "\n",
    "\n",
    "ME['ME']=ME['marginal slope']*ME['P threshold']/ME['Q threshold']\n",
    "ME=ME['ME'].abs()\n",
    "\n",
    "ME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Auction_nr_Comp\n",
       "1.0    244.189751\n",
       "1.0    538.649611\n",
       "1.0    213.321639\n",
       "1.0    190.662802\n",
       "dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#concat all the elasticity dfs and then merge\n",
    "auctions_elasticities = pd.concat((ME,SE,TE,GE), axis=0)\n",
    "auctions_elasticities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
